﻿if exists (select * from dbo.sysobjects where id = 
object_id(N'[dbo].[sp_SalesAnalysisReport]') 
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_SalesAnalysisReport]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[sp_SalesAnalysisReport]
As
Set NoCount On
SELECT 
 Employee.Country,
 Employee.LastName, 
 Employee.FirstName, 
 YEAR(ShippedDate) as [ShippedYearNum], 
 'ShippedQuarterNum' = CASE 
  when MONTH(ShippedDate) between 1 and 3 then 1
  when MONTH(ShippedDate) between 4 and 6 then 2
  when MONTH(ShippedDate) between 7 and 9 then 3
  when MONTH(ShippedDate) between 10 and 12 then 4
 END,
 MONTH(ShippedDate) as [ShippedMonthNum], 
 [Order].ShippedDate, 
 [Order].ID as OrderID,
 [OrderSubtotals].Subtotal AS SaleAmount 
 FROM Employee, [Order],
	(
		SELECT 
		  [OrderDetail].OrderID, 
		  Sum(Cast([UnitPrice]*[Quantity]*(1-[Discount])/100  
			as smallmoney)*100) AS Subtotal
		FROM [OrderDetail]
		GROUP BY [OrderDetail].OrderID
	) OrderSubTotals 
 where
   [Order].ID=[OrderSubtotals].OrderID and 
   Employee.ID=[Order].EmployeeID and
   not ([Order].ShippedDate is null)
order by 
 Employee.LastName, 
 Employee.FirstName,
[Order].ShippedDate, 
[Order].ID
GO

